Display maps with fusion tables and Webviewer

Durée estimée: 45 minutes

Presentation :

You need an App to run your pizza company, that has worldwide customers (with name and address)that order pizzas. You want to see customers location on a map and you customers want to see what’s going on with teir pizza delivery : where’s my pizza ? is it on its way ?

What we will do :

Look at the data organisation and design fusion tables

What we will learn :

Get a small peek at database design,

Input Resources :

Fusion table.

Development :

This development contains 2 different sides

Management of data and maps, which is done in the cloud with fusion tables,
Queries for data and maps and display on smartphone with a webviewer.

Data organisation, fusion tables and attributes

We will first work in the cloud with fusion tables (with our PC's browser) .
What data do we have to handle :

customers with :
name, address and/or latitude-longitude and other fields which you may need, such as distance to the pizza bakery.
orders (taken by customers) with :
date of order, name of customer, pizza ordered, drink, comment and delivery status (ordered, on- delivery, or delivered)

Tables below illustrate the beginning of corresponding fusion tables for customers and orders.

We will do mapping of customers, then mapping of orders as an option.

One click mapping of customers : click on the Map of Location tab and here is your map !

This one click mapping works because Customers have a location criteria which can be either (latitude + longitude) or address :

For this App, we have chosen "latitude and longitude" as the location criteria for performance and privacy reasons. We wish to avoid "addresses" to travel over the web, or to translate addresses to geo-locations at each request.

You can check that "address" is typed as "text" and "Latitude" as "location" with "Longitude" as secondary location info.

NOTE : This table is used by an App which is in the Gallery (“Pizza delivery Mapping with Fusion Tables”) what this App does is to :

convert address to geo locations when creating or updating customer info,
compute distance to pizza shop as last attribute, so that it can be a query parameter,
query and display data as maps or lists.

To understand how this works, we can play wwith selection criteria within fusion tables, for example add criteria on distance, name or address. Here is an example :

In the "Map of location" tab, Click on the Filter button

Select an attribute that you want to filter, for example : Address
Insert criteria, for example : Paris
Then click : Find
- only customers with “Paris” in their address will be displayed
Try again with distance between 0 and 1000 km and name containing “Ma”

Then click : Find
- selection changes, we have customers outside of Paris, but within 1000 km and their name contains “ma”

Last thing to do, before we go back to App Inventor, is to catch the URL which holds this map.

Go back to your fusion table main screen,
Click on the Map of location tab to display map,
Adjust to your area of interest and zoom factor,
Adjust your criteria (for example address and distance)
Click on the Tools tab and Publish option and copy and
Save the link to test in a web browser and reuse with App Inventor Webviewer component.

For our example, we get this URL that you can check in a web browser:

https://fusiontables.google.com/embedviz?q=select+col2+from+1t6jqUO84hs4S1Di3KmAr4EDJiRvwBluNO1mqJ_sC+where+col6+%3E%3D+0+and+col6+%3C%3D+1000+and+col1+contains+ignoring+case+'Paris'&viz=MAP&h=false&lat=48.832371363477534&lng=2.33880412470695&t=1&z=12&l=col2&y=2&tmplt=2&hml=TWO_COL_LAT_LNG

Let us look at it closer (we have replaced <,>, = characters by their readable value)

URL scheme, host and path	https://fusiontables.google.com/embedviz?
Start of query	q=
Select columns or attribute	select+	col2
From TABLE_ID	+from+	1t6jqUO84hs4S1Di3KmAr4EDJiRvwBluNO1mqJ_sC
Where clause (optional)	+where+ +and+	col6+>=+0+and+col6+<=+1000 col1+contains+ignoring+case+'Paris'
Map display type	&viz= &h=	MAP false
Center latitude	&lat=	48.832371363477534
Center longitude	&lng= &t=	2.33880412470695 1
Zoom factor	&z=	11
column for location	&l= &y=	col2 2
Template	&tmplt=	2
Location type (Latitude, longitude)	amp;hml=	TWO_COL_LAT_LNG

Where we see how the query criteria is built with parameters and values.
Keep this canvas in mind, we will reuse it with App Inventor.

(OPTIONAL) Advanced queries/tables : get a map of delivery locations for orders ?

Try the same with the orders fusion table : open it and click on the "Map of Location" tab.
This does not work : display tells you that this table has no location …

This is because, "orders" have no location by themselves, they must be delivered at customer’s location which is in the "customers" table. The trick, to solve this issue is to "Merge" the orders and customers table to get a third table that will contain orders data AND the location of customers.
The merged table is a "dynamic" table which will be automatically updated when you add, remove or modify a customer or an order.
To create this merged table, go to the " orders" table and click on the "merge" command in the file tab, then merge with the customers table, with the "name" as the source for matching on both sides.
This creates a new table "Merge of Customers and orders" where we find :

Now click on the "Map of Latitude" tab and the Map will show orders.

If you add a filter on the pizza delivery status (ex : check Ordered or onDelivery and uncheck Delivered pizzas), only those will show.

If you further click, on an order, you will see all its details.

You may also colour icons according to a numeric variable, but tis exceeds the scope of the current tutorial (see the fusion tables tutorial).

Code "Fusion tables"display project

Would you take this bet ? build an interactive App with NO blocks !!!

Open App Inventor and create a new project,
then add a web viewer component, set its width and height to fill parent, set the Home URL to the URL you saved previously :
https://fusiontables.google.com/embedviz?q=select+col2+from+1t6jqUO84hs4S1Di3KmAr4EDJiRvwBluNO1mqJ_sC+where+col6+%3E%3D+0+and+col6+%3C%3D+1000+and+col1+contains+ignoring+case+'Paris'&viz=MAP&h=false&lat=48.832371363477534&lng=2.33880412470695&t=1&z=12&l=col2&y=2&tmplt=2&hml=TWO_COL_LAT_LNG
Run the App !
No program blocks in this App! But, You can interact with the map :
- click on customer icons to get their name and address,
- zoom, pan,
- switch to satellite display
- or go to streetview with the pegman … !

Now let's go back to the URL and see how we can modify it with program blocks.
We will be looking at 2 kinds of display URL, one for Map display and one for List display (published from the fusion table “Rows” tab) for list format.
Within the SQL query (select, where and order by) we have replaced col0, col1, col2, col4 and col6 by their attribute name : Name, Address, Latitude, Longitude, Distance (this is the order used when we created atble attributes).

		MAP DISPLAY URL	LIST DISPLAY URL
URL scheme, host, path		https://fusiontables.google.com/embedviz?	https://fusiontables.google.com/embedviz?
Start of query	q=
Select cols or attribute	+select+	Latitude	Name,+Address,+Latitude,+Longitude,+Distance
From TABLE_ID	+from+	1t6jqUO84hs4S1Di3KmAr4EDJiRvwBluNO1mqJ_sC	1t6jqUO84hs4S1Di3KmAr4EDJiRvwBluNO1mqJ_sC
Where clause (optional)	+where+	Address+contains+ignoring+case+'Paris'	Address+contains+ignoring+case+'Paris'
And …(other condition)	+and+	Distance>=0+and+Distance<=1000	Distance>=0+and+Distance<=1000
Order by clause (option)	+order+by+		Name+asc
Center latitude	&lat=	48.832371363477534	48.832371363477534
Center longitude	&lng=	2.33880412470695	2.33880412470695
Zoom factor	&z=	11
Map display type	&viz=	MAP	GVIZ
	&t=	1	TABLE
	&h=	false
column for location	&l=	col2
	&y=	2
Template	&tmplt=	2
Location type lat, long	&hml=	TWO_COL_LAT_LNG
Container Id	&containerId	googft-gviz-canvas

Notes :

you can change orders between URL parameters separated by &.
we have kept the + encoding of space in this table to show where blank spaces are
(they must be replaced before calling Uri Encode)
you may want to write the URL with readable characters (instead of “reserved characters” with %3D …), in which case you can ask App Inventor to do the URL encoding with the "Uriencode" block in the "web" component. (I suggest to encode addresses before storing them in fusion tables, in which case you should not encode again)

So that we now have these 2 URLs to test in a program :

MAP display URL
https://fusiontables.google.com/embedviz?q=select+Latitude+from+1t6jqUO84hs4S1Di3KmAr4EDJiRvwBluNO1mqJ_sC+where+Distance>=+0+and+Distance<=+1000+and+Address+contains+ignoring+case+'Paris'&lat=48.832371363477534&lng=2.33880412470695&z=12&viz=MAP&h=false &t=1&l=col2&y=2&tmplt=2&hml=TWO_COL_LAT_LNG
List Display URL
https://fusiontables.google.com/embedviz?q=select+Name,+Address,+Latitude,+Longitude,+Distance+from+1t6jqUO84hs4S1Di3KmAr4EDJiRvwBluNO1mqJ_sC+where+Address+contains+ignoring+case+'Paris'+and+Distance+>=+0+and+Distance+<=+1000+order+by+col0+asc&viz=GVIZ&t=TABLE&containerId=googft-gviz-canvas

Note : above URLs are still using reserved characters such as " = < > ". This is not very clean! But it works, and it's muche easier to read. Most frequent enc< >oding requirment is to use "+" instead of "space".

OK, let's now code these two cases into our App :

Open previous App inventor display project which had no block
create a procedure that computes the URL and start with the previous URL value
Check that it (still) works by setting the Webviewer URL on screen initialize
Then break down the URL into manageable pieces, and build it with joins in the procedure
Note : do this with step by step (checking at each step) to avoid headaches with errors
Replace initial values by variables (for later change by program blocks), check that it works

On the design side :

Add horizontal arrangement with 2 buttons to switch to Map or List display,
Add horizontal arrangement, labels and textboxes for min and Max distance criteria (check numbers only property and set default values to valid values 0 and 21000),
Add horizontal arrangement with label and textbox for text criteria on address (check that default text is the empty string),
Add textbox to show (and check URL) with multiline enabled

Go back to the blocks side :

Create a "whereClause" procedure that builds it from textbox values and returns it,
Replace the "whereClause" variable in the URL procedure by a call to the "whereClause" procedure,
Then add the Map/list button click handlers to set the "selectcolumns" and "endURL" to the right value, and update the textbox with URL.
Run and check (step by step) then set URL textbox visibility off.

Make sure to do things step by step with checks for each change.